USE [TOM_MS]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getSummaryReport]
	/*
	(
	@parameter1 int = 5,
	@parameter2 datatype OUTPUT
	)
	*/
	@seasonId int,
	@toDate date
AS
	/* SET NOCOUNT ON */
	-- get general info of ao

select rs1.ao_season_id as aoSeasonId, rs1.ngaytha as ngayTha,rs1.amount as numberOfTom,rs1.aoCode,rs1.dienTich, rs1.matDo ,rs2.slTom as SLTT,rs2.tomAge, rs2.hoachatName, 
rs2.unitName, rs2.TAActualValue as foodTT,rs2.hcToTalValue,rs2.tomSize as tomsizeTTInGam, rs2.tomSize/1000 as tomsizeTTInKg
from

	(	
		select a1.ao_season_id ,tt.ngaytha,tt.amount,ao.ao_code as aoCode,ao.dien_tich as dienTich, tt.amount/ao.dien_tich as matDo
		from
		(select ao_id, ao_season_id from ao_season where season_id = @seasonId) a1
		join thatom tt
		on (a1.ao_season_id = tt.ao_season_id)
		join ao on (ao.ao_id= a1.ao_id)
		-- lay thong tin thuc an, hoa chat
	) rs1
join	
  (
	select rs1.*,rs2.slTom,rs2.tomSize
	from
		(
		--  tinh tong luong thuc an thuc te den thoi diem hien tai cua ao
			/*begin*/
			select rs1.ao_season_id,rs1.totalValue as TAActualValue,rs1.tomAge, rs2.hoachat_name as hoachatName, rs2.unit_name as unitName,rs2.total as hcToTalValue from
		
			(
			
			select sum(a1.actual_amount) as totalValue, a2.ao_season_id,max(a2.age) as tomAge  from cu_an_his a1
			join choan_log a2 on (a1.choan_id = a2.choan_id) and (a2.created_date <=@toDate) group by a2.ao_season_id
			) rs1,
		
			--lay danh sach hoa chat
			/*begin*/
			(
			select sum (value) as total,a3.hoachat_name, a3.unit_name,a2.ao_season_id from hoachat_his a1
			join choan_log a2 on (a1.choan_id= a2.choan_id)
			join hoachat a3 on (a1.hoachat_id= a3.hoachat_id) group by a2.ao_season_id,a3.hoachat_id,a3.hoachat_name, a3.unit_name
			
			) /*end*/
			rs2 
			where (rs1.ao_season_id= rs2.ao_season_id) 
			/*end*/
		) 
		rs1
		
		join
		-- tinh san luong cua ho tom (sl*tomSize)
		/*begin*/
		(
		select rs1.ao_season_id, (rs1.amount * rs2.value) as slTom, rs2.value as tomSize from
		(
		select a1.amount,a2.ao_season_id from thatom a1
		join choan_log a2 on (a1.ao_season_id= a2.ao_season_id) group by a2.ao_season_id,a1.amount
		) rs1
		join
		-- tinh kich to tom tt hien tai cua tung ao
		(
		/* start*/
		select rs1.*, rs2.value from
		(
		select max(a2.choan_id) as choan_id, a2.ao_season_id
		from choan_log a2  group by a2.ao_season_id
		) rs1
		join ao_tom_size rs2
		on (rs1.choan_id= rs2.choan_id)
		/* end*/
		) 
		rs2 on (rs1.ao_season_id= rs2.ao_season_id)
		)
		/*end*/
		 rs2 
		on (rs1.ao_season_id= rs2.ao_season_id)
	 ) rs2 on (rs1.ao_season_id=rs2.ao_season_id)
	
	 
	RETURN
	
--exec getSummaryReport 1,'2012-12-12'

GO


CREATE PROCEDURE [dbo].[getStandardDataReport]
	/*
	(
	@parameter1 int = 5,
	@parameter2 datatype OUTPUT
	)
	*/
	@numberOfTom int,
	@tomAge int
AS
	/* SET NOCOUNT ON */
	select a1.DKTT as foodDK,a2.tomSize as tomsizeDKInGam, a2.tomSize/1000 as tomsizeDKInKg, a2.tomSize*@numberOfTom/a3.amount as SLDK from
	
	(
	select sum(a2.actual_amount) as DKTT, a1.ao_season_id from
	
	(
	select a1.choan_id, a1.age as tomAge,a1.ao_season_id  from choan_log a1
	join aochuan a2 on (a1.ao_season_id = a2.ao_season_id) and a1.age <=@tomAge
	) a1 join cu_an_his a2 on (a1.choan_id = a2.choan_id) group by  a1.ao_season_id
	) a1
	join
	(
	select max (ao_tom_size.value) as tomSize,choan_log.ao_season_id from ao_tom_size
	join choan_log on (ao_tom_size.choan_id= choan_log.choan_id) and (choan_log.age= @tomAge) group by choan_log.ao_season_id
	) a2 on (a1.ao_season_id =a2.ao_season_id)
	join thatom a3 on (a1.ao_season_id =a2.ao_season_id) 
	
	RETURN
	
	--exec getStandardDataReport 100000,10
	
	

GO



